#!/bin/bash

##################################################
#
#  @desc: dwd.app_event_dtl -> dws.app_pv_agg_session
#  @auth: HUNTER.DUAN
#  @date: 2020-08-11
#  @param_1: 可传指定计算日期
#   
##################################################


export HIVE_HOME=/opt/apps/apache-hive-3.1.2-bin/

datestr=`date -d'-1 day' +%Y-%m-%d`

if [ $1 ]
then
datestr=$1
fi

${HIVE_HOME}/bin/hive -e "
set hive.vectorized.execution.enabled = false;
INSERT INTO TABLE dws.app_pv_agg_session PARTITION (dt = '${datestr}')
SELECT guid,
       sessionid       as session_id,
       min(ts)         as start_time,
       max(ts)         as end_time,
       max(split(area,',')[3]) as first_page,
       max(last_page)  as last_page,
       count(1)        as pv_cnt,
       max(isnew)      as isnew,
       max(split(area,',')[4])       as hour,
       max(split(area,',')[0])   as province,
       max(split(area,',')[1])       as city,
       max(split(area,',')[2])   as district,
       max(devicetype) as device_type

FROM (
         SELECT guid,
                sessionid,
                ts,
                isnew,
                first_value(area) over (partition by sessionid order by ts)     as area,
                last_value(pageid) over (partition by sessionid order by ts)    as last_page,
                devicetype
         FROM (
                  SELECT
                    guid,
                    isnew,
                    properties['pageid'] as pageid,
                    concat_ws(',',nvl(province,'UNKNOWN'),nvl(city,'UNKNOWN'),nvl(district,'UNKNOWN'),nvl(lpad(properties['pageid'],5,'0'),'00000'),nvl(lpad(hour(from_unixtime(cast((ts/1000) as bigint))),2,'0'),'00')) as area  ,
                    sessionid,
                    devicetype,
                    ts
                  FROM dwd.app_event_dtl
                  WHERE dt = '${datestr}'
                    AND eventid = 'pageView' AND properties['pageid'] is not null
              ) o1
     ) o2
GROUP BY guid, sessionid
;

"